set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;

insert overwrite table jms_dm.dm_route_lastest_for_submit
select
     in_from_code
    ,update_date
    ,in_from_provider_code
    ,in_from_city_code
    ,in_from_area_code
    ,out_to_provider_code
    ,out_to_city_code
    ,out_to_area_code
    ,out_to_code
    ,in_branch_id
    ,main_id
    ,out_branch_id
    ,in_collect_code
    ,in_to_code
    ,start_center_code
    ,search_type
    ,start_network_code
    ,in_from_regional_code
    ,in_from_regional_desc
    ,in_from_financial_center_code
    ,in_from_financial_center_desc
    ,in_from_provider_desc
    ,in_from_city_desc
    ,in_from_area_desc
    ,in_collect_name
    ,in_from_name
    ,in_to_name
    ,in_edge_latest_warehousing
    ,in_edge_planned_departure
    ,in_edge_planned_arrival
    ,in_edge2_planned_departure
    ,in_edge2_planned_arrival
    ,branch_in_span_days
    ,branch_in_total_time
    ,is_main_route
    ,transfer_type
    ,num_of_transfer
    ,whole_route
    ,e1_center_flow
    ,e1_line_code
    ,e1_planned_departure
    ,e1_runtime
    ,e1_planned_arrival
    ,e1_e2_stop_time
    ,e2_center_flow
    ,e2_line_code
    ,e2_planned_departure
    ,e2_runtime
    ,e2_planned_arrival
    ,e2_e3_stop_time
    ,e3_center_flow
    ,e3_line_code
    ,e3_planned_departure
    ,e3_runtime
    ,e3_planned_arrival
    ,e3_e4_stop_time
    ,e4_center_flow
    ,e4_line_code
    ,e4_planned_departure
    ,e4_runtime
    ,e4_planned_arrival
    ,e4_e5_stop_time
    ,e5_center_flow
    ,e5_line_code
    ,e5_planned_departure
    ,e5_runtime
    ,e5_planned_arrival
    ,e5_e6_stop_time
    ,e6_center_flow
    ,e6_line_code
    ,e6_planned_departure
    ,e6_runtime
    ,e6_planned_arrival
    ,e6_e7_stop_time
    ,e1_end_code
    ,e2_end_code
    ,e3_end_code
    ,e4_end_code
    ,e5_end_code
    ,e6_end_code
    ,transfer_total_span_days
    ,transfer_total_time_use
    ,main_total_span_days
    ,main_total_time_use
    ,end_center
    ,end_center_code
    ,start_center
    ,out_from_code
    ,out_collect_code
    ,out_to_regional_desc
    ,out_to_regional_code
    ,out_to_financial_center_desc
    ,out_to_financial_center_code
    ,out_to_provider_desc
    ,out_to_city_desc
    ,out_to_area_desc
    ,out_collect_name
    ,out_to_name
    ,out_from_name
    ,out_has_collect
    ,out_edge_planned_departure
    ,out_edge_planned_arrival_time
    ,out_edge2_planned_departure
    ,out_edge2_planned_arrival_time
    ,out_edge2_latest_warehouse_time
    ,out_edge_latest_warehouse_time
    ,out_edge_span_days_sign
    ,out_edge2_span_days_sign
    ,out_edge_deadline_sign_time
    ,out_edge2_deadline_sign_time
    ,branch_out_span_days
    ,branch_out_total_time
    ,effective_date
    ,expiration_date
    ,branch_in_center_stop_time
    ,center_branch_out_stop_time
    ,branch_in_center_span_days
    ,center_branch_out_span_days
    ,is_circuitous
    ,out_edge_span_days_arrive
    ,out_edge2_span_days_arrive
    ,in_nodes
    ,out_nodes
    ,total_days_use
    ,total_time_use
    ,total_days_t
    ,total_nodes
    ,working_days
    ,extra_in_collect_code
    ,extra_in_collect_name
    ,extra_in_line_name
    ,extra_in_latest_warehousing
    ,extra_in_planned_departure
    ,extra_in_planned_arrival
    ,extra_out_collect_code
    ,extra_out_collect_name
    ,extra_out_line_name
    ,extra_out_latest_warehousing
    ,extra_out_planned_departure
    ,extra_out_planned_arrival
    ,in_network_line_name
    ,in_collect_line_name
    ,out_network_line_name
    ,out_collect_line_name
    ,town_plus_time
    ,branch_in_ship_time
    ,branch_in_ship_span_days
    ,branch_out_ship_time
    ,branch_out_ship_span_days
    ,all_line_name
    ,out_town_plus_time
    ,in_edge_run_time
    ,in_edge_span_days
    ,in_edge2_run_time
    ,in_edge2_span_days
    ,in_edge3_run_time
    ,in_edge3_span_days
    ,out_edge_run_time
    ,out_edge_span_days
    ,out_edge2_run_time
    ,out_edge2_span_days
    ,out_edge3_run_time
    ,out_edge3_span_days
    ,e1_span_days
    ,e2_span_days
    ,e3_span_days
    ,e4_span_days
    ,e5_span_days
    ,e6_span_days
    ,delivery_time
    ,route_flow
    ,in_edge2_latest_warehousing
    ,e1_latest_warehousing
    ,e2_latest_warehousing
    ,e3_latest_warehousing
    ,e4_latest_arrival_time
    ,e5_latest_arrival_time
    ,e6_latest_arrival_time
    ,start_taking_shift
    ,end_send_shift
    ,warehouse_end_time
    ,reserve_1
    ,reserve_2
    ,route_contain_main_line
    ,direct_route_type
    ,in_line_day
    ,out_line_day
    ,in_ship_span_days
    ,in_manage_region_code
    ,in_manage_region_name
    ,out_manage_region_code
    ,out_manage_region_name
    ,latest_warehousing_time_quantum
    ,in_edge2_stop_days --集散1到-集散1发_衔接跨天
    ,in_edge3_stop_days --中心1到-中心1发_衔接跨天
    ,first_center_send_time as first_center_plan_send_time --始发中心/末端集散发车时间
    ,dt
from (
    select *,row_number() over(partition by in_from_code,in_collect_code,in_to_code,out_to_code,last_taking_time order by search_type asc,first_network_plan_send_time desc,first_nodel_plan_send_time desc,first_center_plan_send_time desc) as rn
    from (
    select
         *
        ,concat(date_add('{{ execution_date | cst_ds }}',reserve_1+in_line_day),' ',in_edge_planned_departure) as first_network_plan_send_time --始发网点规划发车时间
        ,concat(date_add('{{ execution_date | cst_ds }}',reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days),' ',first_nodel_send_time) as first_nodel_plan_send_time  --始发集散规划发车时间
        ,concat(date_add('{{ execution_date | cst_ds }}',reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_span_days+branch_in_center_span_days+in_edge3_stop_days),' ',first_center_send_time) as first_center_plan_send_time --始发中心规划发车时间
    from (
        select
              *
             ,substr(latest_warehousing_time_quantum,7,5) as last_taking_time --揽收结束时间
             ,case when in_edge2_planned_departure <= in_edge_planned_arrival  then 1 else 0 end as in_edge2_stop_days --始发集散规划到车与始发集散规划发车时间衔接跨天
             ,case when coalesce(e1_planned_departure,out_edge_planned_departure,extra_out_planned_departure,out_edge2_planned_departure) <= nvl(in_edge2_planned_arrival,in_edge_planned_arrival) then 1 else 0 end as in_edge3_stop_days --网点-集散-集散-网点 补充 目的集散衔接跨天
             ,concat(in_edge_planned_departure,':00') as first_network_send_time --始发网点规划发车时间
             ,concat(in_edge2_planned_departure,':00') as first_nodel_send_time --始发集散发车时间
             ,concat(coalesce(e1_planned_departure,out_edge_planned_departure,extra_out_planned_departure,out_edge2_planned_departure),':00') as first_center_send_time --始发中心/末端集散规划发车时间
        from (
             select
                  in_from_code
                 ,update_date
                 ,in_from_provider_code
                 ,in_from_city_code
                 ,in_from_area_code
                 ,out_to_provider_code
                 ,out_to_city_code
                 ,out_to_area_code
                 ,if(length(out_to_code)>0,out_to_code,null) as out_to_code
                 ,in_branch_id
                 ,main_id
                 ,out_branch_id
                 ,if(length(in_collect_code)>0,in_collect_code,null) as in_collect_code
                 ,if(length(in_to_code)>0,in_to_code,null) as in_to_code
                 ,start_center_code
                 ,search_type
                 ,start_network_code
                 ,in_from_regional_code
                 ,in_from_regional_desc
                 ,in_from_financial_center_code
                 ,in_from_financial_center_desc
                 ,in_from_provider_desc
                 ,in_from_city_desc
                 ,in_from_area_desc
                 ,in_collect_name
                 ,in_from_name
                 ,in_to_name
                 ,if(length(in_edge_latest_warehousing)>0,in_edge_latest_warehousing,null) as in_edge_latest_warehousing
                 ,if(length(in_edge_planned_departure )>0,in_edge_planned_departure ,null) as in_edge_planned_departure
                 ,if(length(in_edge_planned_arrival   )>0,in_edge_planned_arrival   ,null) as in_edge_planned_arrival
                 ,case when in_collect_name is not null and if(length(in_edge2_planned_departure)>0,in_edge2_planned_departure,null) is null and in_to_code is not null then e1_planned_departure else in_edge2_planned_departure end as in_edge2_planned_departure
                 ,case when in_collect_name is not null and if(length(in_edge2_planned_arrival  )>0,in_edge2_planned_arrival  ,null) is null and in_to_code is not null then e1_planned_arrival  else in_edge2_planned_arrival end as in_edge2_planned_arrival
                 ,nvl(branch_in_span_days,0) as branch_in_span_days
                 ,branch_in_total_time
                 ,is_main_route
                 ,transfer_type
                 ,num_of_transfer
                 ,whole_route
                 ,if(length(e1_center_flow      )>0,e1_center_flow       ,null) as e1_center_flow
                 ,if(length(e1_line_code        )>0,e1_line_code         ,null) as e1_line_code
                 ,case when in_collect_name is not null and if(length(in_edge2_planned_departure)>0,in_edge2_planned_departure,null) is null and in_to_code is not null then if(length(e2_planned_departure)>0,e2_planned_departure,null)  else e1_planned_departure end as e1_planned_departure
                 ,if(length(e1_runtime          )>0,e1_runtime           ,null) as e1_runtime
                 ,if(length(e1_planned_arrival  )>0,e1_planned_arrival   ,null) as e1_planned_arrival
                 ,if(length(e1_e2_stop_time     )>0,e1_e2_stop_time      ,null) as e1_e2_stop_time
                 ,if(length(e2_center_flow      )>0,e2_center_flow       ,null) as e2_center_flow
                 ,if(length(e2_line_code        )>0,e2_line_code         ,null) as e2_line_code
                 ,if(length(e2_planned_departure)>0,e2_planned_departure ,null) as e2_planned_departure
                 ,if(length(e2_runtime          )>0,e2_runtime           ,null) as e2_runtime
                 ,if(length(e2_planned_arrival  )>0,e2_planned_arrival   ,null) as e2_planned_arrival
                 ,if(length(e2_e3_stop_time     )>0,e2_e3_stop_time      ,null) as e2_e3_stop_time
                 ,if(length(e3_center_flow      )>0,e3_center_flow       ,null) as e3_center_flow
                 ,if(length(e3_line_code        )>0,e3_line_code         ,null) as e3_line_code
                 ,if(length(e3_planned_departure)>0,e3_planned_departure ,null) as e3_planned_departure
                 ,if(length(e3_runtime          )>0,e3_runtime           ,null) as e3_runtime
                 ,if(length(e3_planned_arrival  )>0,e3_planned_arrival   ,null) as e3_planned_arrival
                 ,if(length(e3_e4_stop_time     )>0,e3_e4_stop_time      ,null) as e3_e4_stop_time
                 ,if(length(e4_center_flow      )>0,e4_center_flow       ,null) as e4_center_flow
                 ,if(length(e4_line_code        )>0,e4_line_code         ,null) as e4_line_code
                 ,if(length(e4_planned_departure)>0,e4_planned_departure ,null) as e4_planned_departure
                 ,if(length(e4_runtime          )>0,e4_runtime           ,null) as e4_runtime
                 ,if(length(e4_planned_arrival  )>0,e4_planned_arrival   ,null) as e4_planned_arrival
                 ,if(length(e4_e5_stop_time     )>0,e4_e5_stop_time      ,null) as e4_e5_stop_time
                 ,if(length(e5_center_flow      )>0,e5_center_flow       ,null) as e5_center_flow
                 ,if(length(e5_line_code        )>0,e5_line_code         ,null) as e5_line_code
                 ,if(length(e5_planned_departure)>0,e5_planned_departure ,null) as e5_planned_departure
                 ,if(length(e5_runtime          )>0,e5_runtime           ,null) as e5_runtime
                 ,if(length(e5_planned_arrival  )>0,e5_planned_arrival   ,null) as e5_planned_arrival
                 ,if(length(e5_e6_stop_time     )>0,e5_e6_stop_time      ,null) as e5_e6_stop_time
                 ,if(length(e6_center_flow      )>0,e6_center_flow       ,null) as e6_center_flow
                 ,if(length(e6_line_code        )>0,e6_line_code         ,null) as e6_line_code
                 ,if(length(e6_planned_departure)>0,e6_planned_departure ,null) as e6_planned_departure
                 ,if(length(e6_runtime          )>0,e6_runtime           ,null) as e6_runtime
                 ,if(length(e6_planned_arrival  )>0,e6_planned_arrival   ,null) as e6_planned_arrival
                 ,if(length(e6_e7_stop_time     )>0,e6_e7_stop_time      ,null) as e6_e7_stop_time
                 ,if(length(e1_end_code         )>0,e1_end_code          ,null) as e1_end_code
                 ,if(length(e2_end_code         )>0,e2_end_code          ,null) as e2_end_code
                 ,if(length(e3_end_code         )>0,e3_end_code          ,null) as e3_end_code
                 ,if(length(e4_end_code         )>0,e4_end_code          ,null) as e4_end_code
                 ,if(length(e5_end_code         )>0,e5_end_code          ,null) as e5_end_code
                 ,if(length(e6_end_code         )>0,e6_end_code          ,null) as e6_end_code
                 ,transfer_total_span_days
                 ,transfer_total_time_use
                 ,main_total_span_days
                 ,main_total_time_use
                 ,end_center
                 ,end_center_code
                 ,start_center
                 ,if(length(out_from_code)>0,out_from_code,null) as out_from_code
                 ,if(length(out_collect_code)>0,out_collect_code,null) as out_collect_code
                 ,out_to_regional_desc
                 ,out_to_regional_code
                 ,out_to_financial_center_desc
                 ,out_to_financial_center_code
                 ,out_to_provider_desc
                 ,out_to_city_desc
                 ,out_to_area_desc
                 ,out_collect_name
                 ,out_to_name
                 ,out_from_name
                 ,out_has_collect
                 ,if(length(out_edge_planned_departure)>0,out_edge_planned_departure,null) as out_edge_planned_departure
                 ,if(length(out_edge_planned_arrival_time)>0,out_edge_planned_arrival_time,null) as out_edge_planned_arrival_time
                 ,if(length(out_edge2_planned_departure)>0,out_edge2_planned_departure,null) as out_edge2_planned_departure
                 ,if(length(out_edge2_planned_arrival_time)>0,out_edge2_planned_arrival_time,null) as out_edge2_planned_arrival_time
                 ,if(length(out_edge2_latest_warehouse_time)>0,out_edge2_latest_warehouse_time,null) as out_edge2_latest_warehouse_time
                 ,if(length(out_edge_latest_warehouse_time)>0,out_edge_latest_warehouse_time,null) as out_edge_latest_warehouse_time
                 ,out_edge_span_days_sign
                 ,out_edge2_span_days_sign
                 ,out_edge_deadline_sign_time
                 ,out_edge2_deadline_sign_time
                 ,nvl(branch_out_span_days,0) as branch_out_span_days
                 ,branch_out_total_time
                 ,effective_date
                 ,expiration_date
                 ,branch_in_center_stop_time
                 ,center_branch_out_stop_time
                 ,nvl(branch_in_center_span_days,0) as branch_in_center_span_days
                 ,nvl(center_branch_out_span_days,0) as center_branch_out_span_days
                 ,is_circuitous
                 ,out_edge_span_days_arrive
                 ,out_edge2_span_days_arrive
                 ,in_nodes
                 ,out_nodes
                 ,total_days_use
                 ,total_time_use
                 ,total_days_t
                 ,total_nodes
                 ,working_days
                 ,if(length(extra_in_collect_code       )>0,extra_in_collect_code       ,null) as extra_in_collect_code
                 ,if(length(extra_in_collect_name       )>0,extra_in_collect_name       ,null) as extra_in_collect_name
                 ,if(length(extra_in_line_name          )>0,extra_in_line_name          ,null) as extra_in_line_name
                 ,if(length(extra_in_latest_warehousing )>0,extra_in_latest_warehousing ,null) as extra_in_latest_warehousing
                 ,if(length(extra_in_planned_departure  )>0,extra_in_planned_departure  ,null) as extra_in_planned_departure
                 ,if(length(extra_in_planned_arrival    )>0,extra_in_planned_arrival    ,null) as extra_in_planned_arrival
                 ,if(length(extra_out_collect_code      )>0,extra_out_collect_code      ,null) as extra_out_collect_code
                 ,if(length(extra_out_collect_name      )>0,extra_out_collect_name      ,null) as extra_out_collect_name
                 ,if(length(extra_out_line_name         )>0,extra_out_line_name         ,null) as extra_out_line_name
                 ,if(length(extra_out_latest_warehousing)>0,extra_out_latest_warehousing,null) as extra_out_latest_warehousing
                 ,if(length(extra_out_planned_departure )>0,extra_out_planned_departure ,null) as extra_out_planned_departure
                 ,if(length(extra_out_planned_arrival   )>0,extra_out_planned_arrival   ,null) as extra_out_planned_arrival
                 ,if(length(in_network_line_name        )>0,in_network_line_name        ,null) as in_network_line_name
                 ,if(length(in_collect_line_name        )>0,in_collect_line_name        ,null) as in_collect_line_name
                 ,if(length(out_network_line_name       )>0,out_network_line_name       ,null) as out_network_line_name
                 ,if(length(out_collect_line_name       )>0,out_collect_line_name       ,null) as out_collect_line_name
                 ,town_plus_time
                 ,branch_in_ship_time
                 ,branch_in_ship_span_days
                 ,branch_out_ship_time
                 ,branch_out_ship_span_days
                 ,all_line_name
                 ,out_town_plus_time
                 ,in_edge_run_time
                 ,nvl(in_edge_span_days,0) as in_edge_span_days
                 ,in_edge2_run_time
                 ,nvl(in_edge2_span_days,0) as in_edge2_span_days
                 ,in_edge3_run_time
                 ,nvl(in_edge3_span_days,0) as in_edge3_span_days
                 ,out_edge_run_time
                 ,nvl(out_edge_span_days,0) as out_edge_span_days
                 ,out_edge2_run_time
                 ,nvl(out_edge2_span_days,0) as out_edge2_span_days
                 ,out_edge3_run_time
                 ,nvl(out_edge3_span_days,0) as out_edge3_span_days
                 ,nvl(e1_span_days,0) as e1_span_days
                 ,nvl(e2_span_days,0) as e2_span_days
                 ,nvl(e3_span_days,0) as e3_span_days
                 ,nvl(e4_span_days,0) as e4_span_days
                 ,nvl(e5_span_days,0) as e5_span_days
                 ,nvl(e6_span_days,0) as e6_span_days
                 ,delivery_time
                 ,route_flow
                 ,if(length(in_edge2_latest_warehousing )>0,in_edge2_latest_warehousing ,null) as in_edge2_latest_warehousing
                 ,if(length(e1_latest_warehousing       )>0,e1_latest_warehousing       ,null) as e1_latest_warehousing
                 ,if(length(e2_latest_warehousing       )>0,e2_latest_warehousing       ,null) as e2_latest_warehousing
                 ,if(length(e3_latest_warehousing       )>0,e3_latest_warehousing       ,null) as e3_latest_warehousing
                 ,if(length(e4_latest_arrival_time      )>0,e4_latest_arrival_time      ,null) as e4_latest_arrival_time
                 ,if(length(e5_latest_arrival_time      )>0,e5_latest_arrival_time      ,null) as e5_latest_arrival_time
                 ,if(length(e6_latest_arrival_time      )>0,e6_latest_arrival_time      ,null) as e6_latest_arrival_time
                 ,start_taking_shift
                 ,end_send_shift
                 ,warehouse_end_time
                 ,nvl(reserve_1,0) as reserve_1
                 ,nvl(reserve_2,0) as reserve_2
                 ,route_contain_main_line
                 ,direct_route_type
                 ,nvl(in_line_day,0) as in_line_day
                 ,nvl(out_line_day,0) as out_line_day
                 ,nvl(in_ship_span_days,0) as in_ship_span_days
                 ,in_manage_region_code
                 ,in_manage_region_name
                 ,out_manage_region_code
                 ,out_manage_region_name
                 ,latest_warehousing_time_quantum
                 ,dt
             		     from (
			    select
                     in_from_code
                    ,update_date
                    ,in_from_provider_code
                    ,in_from_city_code
                    ,in_from_area_code
                    ,out_to_provider_code
                    ,out_to_city_code
                    ,out_to_area_code
                    ,if(length(out_to_code)>1,out_to_code,null) as out_to_code
                    ,in_branch_id
                    ,main_id
                    ,out_branch_id
                    ,if(length(in_collect_code)>1,in_collect_code,null) as in_collect_code
                    ,if(length(in_to_code)>1,in_to_code,null) as in_to_code
                    ,start_center_code
                    ,search_type
                    ,start_network_code
                    ,in_from_regional_code
                    ,in_from_regional_desc
                    ,in_from_financial_center_code
                    ,in_from_financial_center_desc
                    ,in_from_provider_desc
                    ,in_from_city_desc
                    ,in_from_area_desc
                    ,in_collect_name
                    ,in_from_name
                    ,in_to_name
                    ,in_edge_latest_warehousing
                    ,in_edge_planned_departure
                    ,in_edge_planned_arrival
                    ,in_edge2_planned_departure
                    ,in_edge2_planned_arrival
                    ,nvl(branch_in_span_days,0) as branch_in_span_days
                    ,branch_in_total_time
                    ,is_main_route
                    ,transfer_type
                    ,num_of_transfer
                    ,whole_route
                    ,if(length(e1_center_flow      )>0,e1_center_flow       ,null) as e1_center_flow
                    ,if(length(e1_line_code        )>0,e1_line_code         ,null) as e1_line_code
                    ,if(length(e1_planned_departure)>0,e1_planned_departure ,null) as e1_planned_departure
                    ,if(length(e1_runtime          )>0,e1_runtime           ,null) as e1_runtime
                    ,if(length(e1_planned_arrival  )>0,e1_planned_arrival   ,null) as e1_planned_arrival
                    ,if(length(e1_e2_stop_time     )>0,e1_e2_stop_time      ,null) as e1_e2_stop_time
                    ,if(length(e2_center_flow      )>0,e2_center_flow       ,null) as e2_center_flow
                    ,if(length(e2_line_code        )>0,e2_line_code         ,null) as e2_line_code
                    ,if(length(e2_planned_departure)>0,e2_planned_departure ,null) as e2_planned_departure
                    ,if(length(e2_runtime          )>0,e2_runtime           ,null) as e2_runtime
                    ,if(length(e2_planned_arrival  )>0,e2_planned_arrival   ,null) as e2_planned_arrival
                    ,if(length(e2_e3_stop_time     )>0,e2_e3_stop_time      ,null) as e2_e3_stop_time
                    ,if(length(e3_center_flow      )>0,e3_center_flow       ,null) as e3_center_flow
                    ,if(length(e3_line_code        )>0,e3_line_code         ,null) as e3_line_code
                    ,if(length(e3_planned_departure)>0,e3_planned_departure ,null) as e3_planned_departure
                    ,if(length(e3_runtime          )>0,e3_runtime           ,null) as e3_runtime
                    ,if(length(e3_planned_arrival  )>0,e3_planned_arrival   ,null) as e3_planned_arrival
                    ,if(length(e3_e4_stop_time     )>0,e3_e4_stop_time      ,null) as e3_e4_stop_time
                    ,if(length(e4_center_flow      )>0,e4_center_flow       ,null) as e4_center_flow
                    ,if(length(e4_line_code        )>0,e4_line_code         ,null) as e4_line_code
                    ,if(length(e4_planned_departure)>0,e4_planned_departure ,null) as e4_planned_departure
                    ,if(length(e4_runtime          )>0,e4_runtime           ,null) as e4_runtime
                    ,if(length(e4_planned_arrival  )>0,e4_planned_arrival   ,null) as e4_planned_arrival
                    ,if(length(e4_e5_stop_time     )>0,e4_e5_stop_time      ,null) as e4_e5_stop_time
                    ,if(length(e5_center_flow      )>0,e5_center_flow       ,null) as e5_center_flow
                    ,if(length(e5_line_code        )>0,e5_line_code         ,null) as e5_line_code
                    ,if(length(e5_planned_departure)>0,e5_planned_departure ,null) as e5_planned_departure
                    ,if(length(e5_runtime          )>0,e5_runtime           ,null) as e5_runtime
                    ,if(length(e5_planned_arrival  )>0,e5_planned_arrival   ,null) as e5_planned_arrival
                    ,if(length(e5_e6_stop_time     )>0,e5_e6_stop_time      ,null) as e5_e6_stop_time
                    ,if(length(e6_center_flow      )>0,e6_center_flow       ,null) as e6_center_flow
                    ,if(length(e6_line_code        )>0,e6_line_code         ,null) as e6_line_code
                    ,if(length(e6_planned_departure)>0,e6_planned_departure ,null) as e6_planned_departure
                    ,if(length(e6_runtime          )>0,e6_runtime           ,null) as e6_runtime
                    ,if(length(e6_planned_arrival  )>0,e6_planned_arrival   ,null) as e6_planned_arrival
                    ,if(length(e6_e7_stop_time     )>0,e6_e7_stop_time      ,null) as e6_e7_stop_time
                    ,if(length(e1_end_code         )>0,e1_end_code          ,null) as e1_end_code
                    ,if(length(e2_end_code         )>0,e2_end_code          ,null) as e2_end_code
                    ,if(length(e3_end_code         )>0,e3_end_code          ,null) as e3_end_code
                    ,if(length(e4_end_code         )>0,e4_end_code          ,null) as e4_end_code
                    ,if(length(e5_end_code         )>0,e5_end_code          ,null) as e5_end_code
                    ,if(length(e6_end_code         )>0,e6_end_code          ,null) as e6_end_code
                    ,transfer_total_span_days
                    ,transfer_total_time_use
                    ,main_total_span_days
                    ,main_total_time_use
                    ,end_center
                    ,end_center_code
                    ,start_center
                    ,if(length(out_from_code)>1,out_from_code,null) as out_from_code
                    ,if(length(out_collect_code)>1,out_collect_code,null) as out_collect_code
                    ,out_to_regional_desc
                    ,out_to_regional_code
                    ,out_to_financial_center_desc
                    ,out_to_financial_center_code
                    ,out_to_provider_desc
                    ,out_to_city_desc
                    ,out_to_area_desc
                    ,out_collect_name
                    ,out_to_name
                    ,out_from_name
                    ,out_has_collect
                    ,out_edge_planned_departure
                    ,out_edge_planned_arrival_time
                    ,if(length(out_edge2_planned_departure)>1,out_edge2_planned_departure,null) as out_edge2_planned_departure
                    ,out_edge2_planned_arrival_time
                    ,if(length(out_edge2_latest_warehouse_time)>1,out_edge2_latest_warehouse_time,null) as out_edge2_latest_warehouse_time
                    ,if(length(out_edge_latest_warehouse_time)>1,out_edge_latest_warehouse_time,null) as out_edge_latest_warehouse_time
                    ,out_edge_span_days_sign
                    ,out_edge2_span_days_sign
                    ,out_edge_deadline_sign_time
                    ,out_edge2_deadline_sign_time
                    ,nvl(branch_out_span_days,0) as branch_out_span_days
                    ,branch_out_total_time
                    ,effective_date
                    ,expiration_date
                    ,branch_in_center_stop_time
                    ,center_branch_out_stop_time
                    ,nvl(branch_in_center_span_days,0) as branch_in_center_span_days
                    ,nvl(center_branch_out_span_days,0) as center_branch_out_span_days
                    ,is_circuitous
                    ,out_edge_span_days_arrive
                    ,out_edge2_span_days_arrive
                    ,in_nodes
                    ,out_nodes
                    ,total_days_use
                    ,total_time_use
                    ,total_days_t
                    ,total_nodes
                    ,working_days
                    ,if(length(extra_in_collect_code       )>0,extra_in_collect_code       ,null) as extra_in_collect_code
                    ,if(length(extra_in_collect_name       )>0,extra_in_collect_name       ,null) as extra_in_collect_name
                    ,if(length(extra_in_line_name          )>0,extra_in_line_name          ,null) as extra_in_line_name
                    ,if(length(extra_in_latest_warehousing )>0,extra_in_latest_warehousing ,null) as extra_in_latest_warehousing
                    ,if(length(extra_in_planned_departure  )>0,extra_in_planned_departure  ,null) as extra_in_planned_departure
                    ,if(length(extra_in_planned_arrival    )>0,extra_in_planned_arrival    ,null) as extra_in_planned_arrival
                    ,if(length(extra_out_collect_code      )>0,extra_out_collect_code      ,null) as extra_out_collect_code
                    ,if(length(extra_out_collect_name      )>0,extra_out_collect_name      ,null) as extra_out_collect_name
                    ,if(length(extra_out_line_name         )>0,extra_out_line_name         ,null) as extra_out_line_name
                    ,if(length(extra_out_latest_warehousing)>0,extra_out_latest_warehousing,null) as extra_out_latest_warehousing
                    ,if(length(extra_out_planned_departure )>0,extra_out_planned_departure ,null) as extra_out_planned_departure
                    ,if(length(extra_out_planned_arrival   )>0,extra_out_planned_arrival   ,null) as extra_out_planned_arrival
                    ,if(length(in_network_line_name        )>0,in_network_line_name        ,null) as in_network_line_name
                    ,if(length(in_collect_line_name        )>0,in_collect_line_name        ,null) as in_collect_line_name
                    ,if(length(out_network_line_name       )>0,out_network_line_name       ,null) as out_network_line_name
                    ,if(length(out_collect_line_name       )>0,out_collect_line_name       ,null) as out_collect_line_name
                    ,town_plus_time
                    ,branch_in_ship_time
                    ,branch_in_ship_span_days
                    ,branch_out_ship_time
                    ,branch_out_ship_span_days
                    ,all_line_name
                    ,out_town_plus_time
                    ,in_edge_run_time
                    ,nvl(in_edge_span_days,0) as in_edge_span_days
                    ,in_edge2_run_time
                    ,nvl(in_edge2_span_days,0) as in_edge2_span_days
                    ,in_edge3_run_time
                    ,nvl(in_edge3_span_days,0) as in_edge3_span_days
                    ,out_edge_run_time
                    ,nvl(out_edge_span_days,0) as out_edge_span_days
                    ,out_edge2_run_time
                    ,nvl(out_edge2_span_days,0) as out_edge2_span_days
                    ,out_edge3_run_time
                    ,nvl(out_edge3_span_days,0) as out_edge3_span_days
                    ,nvl(e1_span_days,0) as e1_span_days
                    ,nvl(e2_span_days,0) as e2_span_days
                    ,nvl(e3_span_days,0) as e3_span_days
                    ,nvl(e4_span_days,0) as e4_span_days
                    ,nvl(e5_span_days,0) as e5_span_days
                    ,nvl(e6_span_days,0) as e6_span_days
                    ,delivery_time
                    ,route_flow
                    ,if(length(in_edge2_latest_warehousing )>0,in_edge2_latest_warehousing ,null) as in_edge2_latest_warehousing
                    ,if(length(e1_latest_warehousing       )>0,e1_latest_warehousing       ,null) as e1_latest_warehousing
                    ,if(length(e2_latest_warehousing       )>0,e2_latest_warehousing       ,null) as e2_latest_warehousing
                    ,if(length(e3_latest_warehousing       )>0,e3_latest_warehousing       ,null) as e3_latest_warehousing
                    ,if(length(e4_latest_arrival_time      )>0,e4_latest_arrival_time      ,null) as e4_latest_arrival_time
                    ,if(length(e5_latest_arrival_time      )>0,e5_latest_arrival_time      ,null) as e5_latest_arrival_time
                    ,if(length(e6_latest_arrival_time      )>0,e6_latest_arrival_time      ,null) as e6_latest_arrival_time
                    ,start_taking_shift
                    ,end_send_shift
                    ,warehouse_end_time
                    ,nvl(reserve_1,0) as reserve_1
                    ,nvl(reserve_2,0) as reserve_2
                    ,route_contain_main_line
                    ,direct_route_type
                    ,nvl(in_line_day,0) as in_line_day
                    ,nvl(out_line_day,0) as out_line_day
                    ,nvl(in_ship_span_days,0) as in_ship_span_days
                    ,in_manage_region_code
                    ,in_manage_region_name
                    ,out_manage_region_code
                    ,out_manage_region_name
                    ,latest_warehousing_time_quantum
                    ,dt
                from jms_dm.dm_route_whole_base_dt
                where dt = '{{ execution_date | cst_ds }}'
                and in_from_name is not null
                and out_to_code is not null
                and total_time_use > 0
                and length(in_edge_latest_warehousing) > 0
                and is_circuitous = 0
                and nvl(route_contain_main_line,0) <> 1 --剔除直发
                and in_branch_id is not null --剔除直发
			    union all
			    select
					in_from_code
                    ,update_date
                    ,in_from_provider_code
                    ,in_from_city_code
                    ,in_from_area_code
                    ,out_to_provider_code
                    ,out_to_city_code
                    ,out_to_area_code
                    ,if(length(out_to_code)>1,out_to_code,null) as out_to_code
                    ,in_branch_id
                    ,main_id
                    ,out_branch_id
                    ,if(length(in_collect_code)>1,in_collect_code,null) as in_collect_code
                    ,if(length(in_to_code)>1,in_to_code,null) as in_to_code
                    ,start_center_code
                    ,search_type
                    ,start_network_code
                    ,in_from_regional_code
                    ,in_from_regional_desc
                    ,in_from_financial_center_code
                    ,in_from_financial_center_desc
                    ,in_from_provider_desc
                    ,in_from_city_desc
                    ,in_from_area_desc
                    ,in_collect_name
                    ,in_from_name
                    ,in_to_name
                    ,in_edge_latest_warehousing
                    ,in_edge_planned_departure
                    ,in_edge_planned_arrival
                    ,in_edge2_planned_departure
                    ,in_edge2_planned_arrival
                    ,nvl(branch_in_span_days,0) as branch_in_span_days
                    ,branch_in_total_time
                    ,is_main_route
                    ,transfer_type
                    ,num_of_transfer
                    ,whole_route
                    ,if(length(e1_center_flow      )>0,e1_center_flow       ,null) as e1_center_flow
                    ,if(length(e1_line_code        )>0,e1_line_code         ,null) as e1_line_code
                    ,if(length(e1_planned_departure)>0,e1_planned_departure ,null) as e1_planned_departure
                    ,if(length(e1_runtime          )>0,e1_runtime           ,null) as e1_runtime
                    ,if(length(e1_planned_arrival  )>0,e1_planned_arrival   ,null) as e1_planned_arrival
                    ,if(length(e1_e2_stop_time     )>0,e1_e2_stop_time      ,null) as e1_e2_stop_time
                    ,if(length(e2_center_flow      )>0,e2_center_flow       ,null) as e2_center_flow
                    ,if(length(e2_line_code        )>0,e2_line_code         ,null) as e2_line_code
                    ,if(length(e2_planned_departure)>0,e2_planned_departure ,null) as e2_planned_departure
                    ,if(length(e2_runtime          )>0,e2_runtime           ,null) as e2_runtime
                    ,if(length(e2_planned_arrival  )>0,e2_planned_arrival   ,null) as e2_planned_arrival
                    ,if(length(e2_e3_stop_time     )>0,e2_e3_stop_time      ,null) as e2_e3_stop_time
                    ,if(length(e3_center_flow      )>0,e3_center_flow       ,null) as e3_center_flow
                    ,if(length(e3_line_code        )>0,e3_line_code         ,null) as e3_line_code
                    ,if(length(e3_planned_departure)>0,e3_planned_departure ,null) as e3_planned_departure
                    ,if(length(e3_runtime          )>0,e3_runtime           ,null) as e3_runtime
                    ,if(length(e3_planned_arrival  )>0,e3_planned_arrival   ,null) as e3_planned_arrival
                    ,if(length(e3_e4_stop_time     )>0,e3_e4_stop_time      ,null) as e3_e4_stop_time
                    ,if(length(e4_center_flow      )>0,e4_center_flow       ,null) as e4_center_flow
                    ,if(length(e4_line_code        )>0,e4_line_code         ,null) as e4_line_code
                    ,if(length(e4_planned_departure)>0,e4_planned_departure ,null) as e4_planned_departure
                    ,if(length(e4_runtime          )>0,e4_runtime           ,null) as e4_runtime
                    ,if(length(e4_planned_arrival  )>0,e4_planned_arrival   ,null) as e4_planned_arrival
                    ,if(length(e4_e5_stop_time     )>0,e4_e5_stop_time      ,null) as e4_e5_stop_time
                    ,if(length(e5_center_flow      )>0,e5_center_flow       ,null) as e5_center_flow
                    ,if(length(e5_line_code        )>0,e5_line_code         ,null) as e5_line_code
                    ,if(length(e5_planned_departure)>0,e5_planned_departure ,null) as e5_planned_departure
                    ,if(length(e5_runtime          )>0,e5_runtime           ,null) as e5_runtime
                    ,if(length(e5_planned_arrival  )>0,e5_planned_arrival   ,null) as e5_planned_arrival
                    ,if(length(e5_e6_stop_time     )>0,e5_e6_stop_time      ,null) as e5_e6_stop_time
                    ,if(length(e6_center_flow      )>0,e6_center_flow       ,null) as e6_center_flow
                    ,if(length(e6_line_code        )>0,e6_line_code         ,null) as e6_line_code
                    ,if(length(e6_planned_departure)>0,e6_planned_departure ,null) as e6_planned_departure
                    ,if(length(e6_runtime          )>0,e6_runtime           ,null) as e6_runtime
                    ,if(length(e6_planned_arrival  )>0,e6_planned_arrival   ,null) as e6_planned_arrival
                    ,if(length(e6_e7_stop_time     )>0,e6_e7_stop_time      ,null) as e6_e7_stop_time
                    ,if(length(e1_end_code         )>0,e1_end_code          ,null) as e1_end_code
                    ,if(length(e2_end_code         )>0,e2_end_code          ,null) as e2_end_code
                    ,if(length(e3_end_code         )>0,e3_end_code          ,null) as e3_end_code
                    ,if(length(e4_end_code         )>0,e4_end_code          ,null) as e4_end_code
                    ,if(length(e5_end_code         )>0,e5_end_code          ,null) as e5_end_code
                    ,if(length(e6_end_code         )>0,e6_end_code          ,null) as e6_end_code
                    ,transfer_total_span_days
                    ,transfer_total_time_use
                    ,main_total_span_days
                    ,main_total_time_use
                    ,end_center
                    ,end_center_code
                    ,start_center
                    ,if(length(out_from_code)>1,out_from_code,null) as out_from_code
                    ,if(length(out_collect_code)>1,out_collect_code,null) as out_collect_code
                    ,out_to_regional_desc
                    ,out_to_regional_code
                    ,out_to_financial_center_desc
                    ,out_to_financial_center_code
                    ,out_to_provider_desc
                    ,out_to_city_desc
                    ,out_to_area_desc
                    ,out_collect_name
                    ,out_to_name
                    ,out_from_name
                    ,out_has_collect
                    ,out_edge_planned_departure
                    ,out_edge_planned_arrival_time
                    ,if(length(out_edge2_planned_departure)>1,out_edge2_planned_departure,null) as out_edge2_planned_departure
                    ,out_edge2_planned_arrival_time
                    ,if(length(out_edge2_latest_warehouse_time)>1,out_edge2_latest_warehouse_time,null) as out_edge2_latest_warehouse_time
                    ,if(length(out_edge_latest_warehouse_time)>1,out_edge_latest_warehouse_time,null) as out_edge_latest_warehouse_time
                    ,out_edge_span_days_sign
                    ,out_edge2_span_days_sign
                    ,out_edge_deadline_sign_time
                    ,out_edge2_deadline_sign_time
                    ,nvl(branch_out_span_days,0) as branch_out_span_days
                    ,branch_out_total_time
                    ,effective_date
                    ,expiration_date
                    ,branch_in_center_stop_time
                    ,center_branch_out_stop_time
                    ,nvl(branch_in_center_span_days,0) as branch_in_center_span_days
                    ,nvl(center_branch_out_span_days,0) as center_branch_out_span_days
                    ,is_circuitous
                    ,out_edge_span_days_arrive
                    ,out_edge2_span_days_arrive
                    ,in_nodes
                    ,out_nodes
                    ,total_days_use
                    ,total_time_use
                    ,total_days_t
                    ,total_nodes
                    ,working_days
                    ,if(length(extra_in_collect_code       )>0,extra_in_collect_code       ,null) as extra_in_collect_code
                    ,if(length(extra_in_collect_name       )>0,extra_in_collect_name       ,null) as extra_in_collect_name
                    ,if(length(extra_in_line_name          )>0,extra_in_line_name          ,null) as extra_in_line_name
                    ,if(length(extra_in_latest_warehousing )>0,extra_in_latest_warehousing ,null) as extra_in_latest_warehousing
                    ,if(length(extra_in_planned_departure  )>0,extra_in_planned_departure  ,null) as extra_in_planned_departure
                    ,if(length(extra_in_planned_arrival    )>0,extra_in_planned_arrival    ,null) as extra_in_planned_arrival
                    ,if(length(extra_out_collect_code      )>0,extra_out_collect_code      ,null) as extra_out_collect_code
                    ,if(length(extra_out_collect_name      )>0,extra_out_collect_name      ,null) as extra_out_collect_name
                    ,if(length(extra_out_line_name         )>0,extra_out_line_name         ,null) as extra_out_line_name
                    ,if(length(extra_out_latest_warehousing)>0,extra_out_latest_warehousing,null) as extra_out_latest_warehousing
                    ,if(length(extra_out_planned_departure )>0,extra_out_planned_departure ,null) as extra_out_planned_departure
                    ,if(length(extra_out_planned_arrival   )>0,extra_out_planned_arrival   ,null) as extra_out_planned_arrival
                    ,if(length(in_network_line_name        )>0,in_network_line_name        ,null) as in_network_line_name
                    ,if(length(in_collect_line_name        )>0,in_collect_line_name        ,null) as in_collect_line_name
                    ,if(length(out_network_line_name       )>0,out_network_line_name       ,null) as out_network_line_name
                    ,if(length(out_collect_line_name       )>0,out_collect_line_name       ,null) as out_collect_line_name
                    ,town_plus_time
                    ,branch_in_ship_time
                    ,branch_in_ship_span_days
                    ,branch_out_ship_time
                    ,branch_out_ship_span_days
                    ,all_line_name
                    ,out_town_plus_time
                    ,in_edge_run_time
                    ,nvl(in_edge_span_days,0) as in_edge_span_days
                    ,in_edge2_run_time
                    ,nvl(in_edge2_span_days,0) as in_edge2_span_days
                    ,in_edge3_run_time
                    ,nvl(in_edge3_span_days,0) as in_edge3_span_days
                    ,out_edge_run_time
                    ,nvl(out_edge_span_days,0) as out_edge_span_days
                    ,out_edge2_run_time
                    ,nvl(out_edge2_span_days,0) as out_edge2_span_days
                    ,out_edge3_run_time
                    ,nvl(out_edge3_span_days,0) as out_edge3_span_days
                    ,nvl(e1_span_days,0) as e1_span_days
                    ,nvl(e2_span_days,0) as e2_span_days
                    ,nvl(e3_span_days,0) as e3_span_days
                    ,nvl(e4_span_days,0) as e4_span_days
                    ,nvl(e5_span_days,0) as e5_span_days
                    ,nvl(e6_span_days,0) as e6_span_days
                    ,delivery_time
                    ,route_flow
                    ,if(length(in_edge2_latest_warehousing )>0,in_edge2_latest_warehousing ,null) as in_edge2_latest_warehousing
                    ,if(length(e1_latest_warehousing       )>0,e1_latest_warehousing       ,null) as e1_latest_warehousing
                    ,if(length(e2_latest_warehousing       )>0,e2_latest_warehousing       ,null) as e2_latest_warehousing
                    ,if(length(e3_latest_warehousing       )>0,e3_latest_warehousing       ,null) as e3_latest_warehousing
                    ,if(length(e4_latest_arrival_time      )>0,e4_latest_arrival_time      ,null) as e4_latest_arrival_time
                    ,if(length(e5_latest_arrival_time      )>0,e5_latest_arrival_time      ,null) as e5_latest_arrival_time
                    ,if(length(e6_latest_arrival_time      )>0,e6_latest_arrival_time      ,null) as e6_latest_arrival_time
                    ,start_taking_shift
                    ,end_send_shift
                    ,warehouse_end_time
                    ,nvl(reserve_1,0) as reserve_1
                    ,nvl(reserve_2,0) as reserve_2
                    ,route_contain_main_line
                    ,direct_route_type
                    ,nvl(in_line_day,0) as in_line_day
                    ,nvl(out_line_day,0) as out_line_day
                    ,nvl(in_ship_span_days,0) as in_ship_span_days
                    ,in_manage_region_code
                    ,in_manage_region_name
                    ,out_manage_region_code
                    ,out_manage_region_name
                    ,latest_warehousing_time_quantum
                    ,dt
				from jms_dm.dm_route_whole_direct_base_dt
				where dt = '{{ execution_date | cst_ds }}'
				and in_from_name is not null
				and out_to_code is not null
			) a
          ) a
      ) a
   ) a
) a where a.rn = 1
;